﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using Microsoft.Reporting.WebForms;
using HK2009.Common;
using HK2009.BusinessFacade;
using HK2009.BusinessFacadeHandler;
using HK2009.EntityQuery;
using System.Data.SqlClient;
using System.Text;
using System.Collections.Generic;

public partial class Reports_TemporaryReceipt : System.Web.UI.Page
{
     DataBase db=new DataBase();
     ISystemHandler sysManage = new SystemHandler();

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            ISystemHandler sysManage = new SystemHandler();
            string strPermission = sysManage.GetLoginUserPermissions();
            if (!(strPermission.Contains("1070")))
            {
                Response.Redirect("~/NotPurview.aspx");
                return;
            }      

            PreviewReport();
        }
    }

    #region GetDataTable 获取主报表的数据源
    private DataTable GetDataTable(string orderID,string status)
    {
        SqlParameter[] parms = new SqlParameter[2];
        parms[0] = new SqlParameter("@OrderID",orderID);
        parms[1] = new SqlParameter("@Status",status);

        string sqlStr = @"SELECT SO.*,     
                        SoDtl.JobDate,
                        (SELECT HKName
                         FROM Sy_Client AS Sy_Client_3
                         WHERE (PKID = SO.VendorID)) AS VendorName,
                         (SELECT HKAddress
                         FROM Sy_Client AS Sy_Client_2
                        WHERE (PKID = SO.VendorID)) AS VendorAddr,
		                (select LinkMan  FROM Sy_ClientLinkMan where PKID=(select VendorLinkMan from BN_BookingNoteDtl where PKID=(select BookingNoteDtlID from BN_BookingDtl where PKID=(select BookingID from BN_ShippingOrder where PKID=@OrderID )) ))as   VendorLinkMan,      
 		                (select TelNo  FROM Sy_ClientLinkMan where PKID=(select VendorLinkMan from BN_BookingNoteDtl where PKID=(select BookingNoteDtlID from BN_BookingDtl where PKID=(select BookingID from BN_ShippingOrder where PKID=@OrderID )) ))AS VendorTel,
                        (SELECT [values] FROM(
            SELECT [values] = STUFF(REPLACE(REPLACE((
               SELECT DISTINCT LicenseNO FROM BN_SendTruck st INNER JOIN
                    BN_SendTruckDtl stDtl ON st.PKID = SendTruckID INNER JOIN
                    BN_Truck truck ON stDtl.TruckID = truck.PKID inner join
 BN_ShippingOrder AS SO on SO.PKID=ST.OrderID
                WHERE   (st.OrderID=@OrderID or  OrderID in(select OrderID from BN_SendTruck where SendCode=(select SendCode from BN_SendTruck where OrderID=@OrderID))) and
                    st.Contractor = SO.Contractor
                FOR XML AUTO
            ), '<truck LicenseNO=" + "\"" + "', ', '),'" + "\"" + @"/>', ''),1,1,'')
        )N) as LicenseNO  ,

     (SELECT [values] FROM(
            SELECT [values] = STUFF(REPLACE(REPLACE((
                SELECT DISTINCT '[' + ENGName + ']' as ShipperName FROM Sy_Client as SC where SC.PKID in(select ShipperID from
				BN_ShippingOrderDtl where OrderID in
				(select OrderID from BN_SendTruck where 
					SendCode=(select SendCode from BN_SendTruck where OrderID=@OrderID)))
                    
                FOR XML AUTO
            ), '<SC ShipperName=" + "\"" + "', ', '),'" + "\"" + @"/>', ''),1,1,'')
        )N) as ShipperName  ,

                        (SELECT HKName
                          FROM Sy_Client AS Sy_Client_1
                            WHERE (PKID = SO.Contractor)) AS ContractorName,
	                    (select top 1 LinkMan  FROM Sy_ClientLinkMan where ClientID =SO.Contractor) AS ContractorLinkMan,
                        (select top 1 TelNo  FROM Sy_ClientLinkMan where ClientID =SO.Contractor) AS ContractorTelNO,
                        (select top 1 MPhone  FROM Sy_ClientLinkMan where ClientID =SO.Contractor) AS ContractorPhone,
                        (select top 1 Fax  FROM Sy_ClientLinkMan where ClientID =SO.Contractor) AS ContractorFax,
                        (SELECT [values] FROM(
            SELECT [values] = STUFF(REPLACE(REPLACE((
                SELECT DISTINCT HKName as Whouse FROM Sy_Client N WHERE PKID IN (
                    SELECT WhouseID FROM BN_ShippingOrderDtl WHERE OrderID = SO.PKID
                ) FOR XML AUTO
            ), '<N Whouse=" + "\"" + "', ', '), '" + "\"" + @"/>', ''), 1, 1, '')
        )N) as Whouse    


FROM BN_ShippingOrder AS SO,BN_ShippingOrderDtl as SoDtl where SO.PKID=SoDtl.OrderID and SO.PKID=@OrderID and SO.Status=@Status  ";

        return db.RunSqlTab(sqlStr, parms);
    }

    #endregion


    #region SetReportParameters
    private void SetReportParameters()
    {
        SystemConfigQuery linkManconfig = sysManage.GetConfig("CompanyLinkMan");       

        SystemConfigQuery telQuery = sysManage.GetConfig("CompanyTel");        

        SystemConfigQuery phoneQuery = sysManage.GetConfig("CompanyPhone");        

        SystemConfigQuery faxQuery = sysManage.GetConfig("CompanyFax");        
       
        ReportParameter[] parameters = new ReportParameter[4];
        parameters[0] = new ReportParameter("LinkMan", linkManconfig.ConfigValue);
        parameters[1] = new ReportParameter("Tel", telQuery.ConfigValue);
        parameters[2] = new ReportParameter("Mobile", phoneQuery.ConfigValue);
        parameters[3] = new ReportParameter("Fax", faxQuery.ConfigValue);
        this.rptViewer.LocalReport.SetParameters(parameters);
    }
    #endregion

    #region Preview Report
    private void PreviewReport()
    {
        if (Request["OrderID"] != null && Request["Status"] != null)
        {          

            rptViewer.LocalReport.ReportPath = "Reports/TemporaryReceipt.rdlc";

            rptViewer.LocalReport.DataSources.Clear();
            rptViewer.LocalReport.SubreportProcessing += new SubreportProcessingEventHandler(rptSubreportProcessingEventHandler);
            ReportDataSource datasource = new ReportDataSource("TemporaryReceipt_BN_ShippingOrder", GetDataTable(Request["OrderID"],Request["Status"]));

            rptViewer.LocalReport.DataSources.Add(datasource);

          //  SetReportParameters();


            rptViewer.LocalReport.Refresh();

            //修改shippingorder状态

            SqlParameter[] parms = new SqlParameter[5];
            parms[0] = new SqlParameter("@Status", Enums.ShippingOrderStatus.PRINT.ToString("d"));
            parms[1] = new SqlParameter("@PrintDate", DateTime.Now);
            parms[2] = new SqlParameter("@PrintUser", Session["UserID"].ToString());
            parms[3] = new SqlParameter("@OrderID", Request["OrderID"]);
            parms[4] = new SqlParameter("@BookingNoteDtlStatus",Enums.BookingNoteDtlStatus.PRINTTEMP.ToString("d"));

            string sqlStr = "update BN_ShippingOrder set Status=@Status,PrintDate=@PrintDate,PrintUser=@PrintUser where PKID=@OrderID;";
            sqlStr += "update BN_BookingNoteDtl set Status=@BookingNoteDtlStatus,PrintDate=@PrintDate,PrintUser=@PrintUser where PKID in(select BookingNoteDtlID from BN_BookingDtl where PKID in(select BookingDtlID from BN_ShippingOrderDtl where OrderID=@OrderID))";
            db.ExecSqlTransaction(sqlStr, parms);
        }
    }
    #endregion

    #region GetDetail 获取子报表的数据源
    private DataTable GetDetail()
    {
        SqlParameter[] parms=new SqlParameter[1];
       // parms[0] =new SqlParameter("@ShipperID",shipperID.ToString());
        parms[0] = new SqlParameter("@OrderID", Request["OrderID"]);
       // parms[2] = new SqlParameter("@LicenseNO",licenseNO);
       // parms[2] = new SqlParameter("@WhouseID", whouseID.ToString());
        string sqlStr = @"
                    select (rownumber-1)/3 as RowNumberO,
                    max(a1) as CTN1,max(s1) as SONO1,
                    max(a2)as CTN2,max(s2)as SONO2,
                    max(a3)as CTN3,max(s3)as SONO3,
                    max(v1) as VOL1,
                    max(v2) as VOL2,
                    max(v3)as VOL3
                    from(
                    select rownumber,case (rownumber%3) when 1 then ctn else null end as a1,
                    case (rownumber%3) when 1 then SONO else null end as s1,
                    case (rownumber%3) when 2 then ctn else null end as a2,
                    case (rownumber%3) when 2 then SONO else null end as s2,
                    case (rownumber%3) when 0 then ctn else null end as a3,
                    case (rownumber%3) when 0 then SONO else null end as s3,
                    case (rownumber%3) when 1 then VOL else null end as v1,
                    case (rownumber%3) when 2 then VOL else null end as v2,
                    case (rownumber%3) when 0 then VOL else null end as v3
                    
                from
                    (select ROW_NUMBER() over(order by pkid desc) rownumber,                    
                    * from BN_ShippingOrderDtl where OrderID in(select OrderID from BN_SendTruck where SendCode=(select SendCode from BN_SendTruck where OrderID=@OrderID))) as aaa
                    ) as bbb
                    group by (rownumber-1)/3";      

         
        DataTable dt= db.RunSqlTab(sqlStr, parms);
        if (dt != null && dt.Rows.Count > 0)
        {
            int num = dt.Rows.Count;
            for (int i = 0; i < 12 - num; i++)
            {
                dt.Rows.Add(new string[] {
                   "0",
                    "0",
                    "",
                    "0",
                    "",
                    "0",
                    "",
                    "0",
                    "0",
                    "0"

            }

                    );
            }
        }
        return dt;
    }
    #endregion


    /// <summary>
    /// 子报表事件
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    void rptSubreportProcessingEventHandler(object sender, SubreportProcessingEventArgs e)
    {
       // int shipperID = int.Parse(e.Parameters["ShipperID"].Values[0].Trim());  //获取子报表的参数值   
      //  string LicenseNO = e.Parameters["LicenseNO"].Values[0].Trim();
        //int whouseID = int.Parse(e.Parameters["WhouseID"].Values[0].Trim());
        e.DataSources.Add(new ReportDataSource("SubTemporaryReceipt_BN_ShippingOrderDtl", GetDetail()));
    }
    
    
}
